﻿/*********************************************
 创建者：  dmhai
 创建时间：2017-04-25
 功能说明：店铺颜色占比
 ********************************************/
using com.nm.ApiServer;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.Helper;
using com.nm.library;
using com.nm.library.Helper;
using george.Data;
using george.Helper;
using PlanAPI.model;
using NHibernateVertica;
using Vertica.Data.VerticaClient;

namespace PlanAPI
{
    [Description("店铺颜色占比")]
    public class PLBasShopColorRateHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        #region GetList

        [MustLogin]
        [Description("获取店铺颜色占比列表")]
        [ReturnType(typeof(PaperData<PLBasShopColorRateModel.ListModel>))]
        public ApiResult GetList(
         [ParamDescription("年份")] string Yearv = "",
         [ParamDescription("季节")] string SeasonID = "",
         [ParamDescription("商品区域")] string ParentAreaID = "",
         [ParamDescription("店铺ID集合")] string Shop = "",
         [ParamDescription("页码")] int Page = 1,
         [ParamDescription("页数")] int Size = 100)
        {
            try
            {
                var sqlStr = @"select {0} from ( 
select scr.*,shop.ShopCode,shop.ShopName,season.SeasonName
,row_number()over(order by scr.Yearv,scr.SeasonID,scr.ParentAreaID,scr.ShopID) as rowIndex
from ngoods.ngoods_PL_Bas_ShopColorRate scr
left join ndw.ndw_Shop shop on scr.CompanyID = shop.CompanyID and scr.ShopID = shop.ShopID
left join nods.nods_erp_sd_mat_season season on scr.CompanyID = season.ExtrSource and scr.SeasonID = season.SeasonID
{1} 
order by scr.Yearv,scr.SeasonID,scr.ParentAreaID,shop.ShopCode
) table1 
";
                string whereStr = $"where scr.CompanyID = '{UserInfo.UserCompanyId}' and 1 = 1 ";
                //加入前台传来的参数
                whereStr += string.IsNullOrWhiteSpace(Yearv) ? "" : $" and scr.Yearv = {Yearv}";
                whereStr += string.IsNullOrWhiteSpace(SeasonID) ? "" : $" and scr.SeasonID = '{SeasonID}'";
                whereStr += string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $" and scr.ParentAreaID = '{ParentAreaID}'";
                if (!string.IsNullOrWhiteSpace(Shop))
                {
                    Shop = "'" + Shop.Replace(",", "','").Trim('\'') + "'";
                    whereStr += $" and shop.ShopID in ({Shop}) ";
                }


               

                var countSql = string.Format(sqlStr, "count(1)", whereStr);
                var cmd = Helper.GetSqlStringCommand(countSql);
                var count = Convert.ToInt32(Helper.ExecuteScalar(cmd));


                var result = PaperData<PLBasShopColorRateModel.ListModel>.Create(count, Size, Page);
                var listSql = string.Format(sqlStr, "*", whereStr);
                if (Page != 0 && Size != 0)
                {
                    listSql += string.Format($@" where rowIndex BETWEEN ({Page}-1)*{Size}+1 AND {Page}*{Size} ");
                }

                cmd = Helper.GetSqlStringCommand(listSql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<PLBasShopColorRateModel.ListModel>.ConvertToModel(dt);
                result.Items = list;
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }


        #endregion

        #region Insert

        [MustLogin]
        [Description("插入店铺颜色占比")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult Insert(
            [ParamDescription("公司代码")] string CompanyID = "KM",
            [ParamDescription("年份")] int Yearv = 0,
            [ParamDescription("季节")] string SeasonID = "",
            [ParamDescription("商品区域")] string ParentAreaID = "",
            [ParamDescription("店铺编码或名称")] string Shop = "",
            [ParamDescription("流行色比例")] decimal PopularRate = 0,
            [ParamDescription("基本色比例")] decimal BasicRate = 0)
        {
            #region 前端参数验证

            if (Yearv <= 0)
            {
                return Error($"插入失败！年份参数丢失，请刷新页面重试");
            }
            if (string.IsNullOrWhiteSpace(SeasonID))
            {
                return Error($"插入失败！季节参数丢失，请刷新页面重试");
            }

            if (string.IsNullOrWhiteSpace(ParentAreaID))
            {
                return Error($"插入失败！商品区域参数丢失，请刷新页面重试");
            }

            if (string.IsNullOrWhiteSpace(Shop))
            {
                return Error($"插入失败！店铺不能为空，请输入店铺的名称或者编码");
            }

            if (PopularRate < 0)
            {
                return Error($"插入失败！流行色占比不能小于0");
            }

            if (BasicRate < 0)
            {
                return Error("插入失败！基本色占比不能小于0");
            }

            #endregion

            #region 检测店铺是否存在并获取shopId

            var sql = $@"select ShopID
--,ShopCode,ShopName,gv.dictValue as DistrictValue,gv.dictName as DistrictName
from ndw.ndw_Shop dw 
left join ndw.ndw_AreaProCity apc on dw.CompanyID = apc.CompanyID and dw.AreaID = apc.AreaID 
left join ndw.ndw_global_dictionary gv on apc.CompanyID = gv.CompanyID and apc.ParentAreaID = gv.dictValue 
where apc.CompanyID = '{UserInfo.UserCompanyId}' and gv.dictValue = '{ParentAreaID}' and (ShopCode = '{Shop}' or ShopName = '{Shop}')  limit 1 ";

            var o = Helper.ExecuteScalar(new VerticaCommand(sql));

            if (o == null)
            {
                return Error($"[{ParentAreaID}]不存在店铺[{Shop}],请检查店铺名称");
            }

            var shopId = string.Concat(o);

            #endregion

            #region 检测该配置是否已存在，避免插入重复数据

            sql = "select ShopID from ngoods.ngoods_PL_Bas_ShopColorRate " +
                  $"where CompanyID = '{CompanyID}' and " +
                  $"Yearv = {Yearv} and " +
                  $"SeasonID = '{SeasonID}' and " +
                  $"ParentAreaID = '{ParentAreaID}' and " +
                  $"ShopID = '{shopId}'  " +
                  "limit 1 ";
            o = Helper.ExecuteScalar(new VerticaCommand(sql));

            if (o != null)
            {
                return Error($"新增失败！\r\n同年份、同季节、同区域、同店铺的配置已存在！");
            }


            #endregion

            #region 插入新数据

            sql = $@"insert into 
ngoods.ngoods_PL_Bas_ShopColorRate(ExtrSource,CompanyID, Yearv, SeasonID, ParentAreaID, ShopID, PopularRate, BasicRate, CheckState, MofifyDTM, AllowUsed) 
values('{UserInfo.UserExtrSource}','{CompanyID}', {Yearv}, '{SeasonID}', '{ParentAreaID}', '{shopId}', {PopularRate / 100}, {BasicRate / 100}, 0, '{DateTime.Now}', 1) ";

            int n = Helper.ExecuteNonQuery(new VerticaCommand(sql));
            #endregion

            if (n > 0)
            {
                return Success(0);
            }
            return Error("插入失败,请重试");
        }
        #endregion

        #region Update

        [MustLogin]
        [Description("更新店铺颜色占比")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult Update(
            [ParamDescription("公司代码")] string CompanyID = "KM",
            [ParamDescription("年份")] int Yearv = 0,
            [ParamDescription("季节")] string SeasonID = "",
            [ParamDescription("商品区域")] string ParentAreaID = "",
            [ParamDescription("店铺或名称")] string Shop = "",
            [ParamDescription("流行色比例")] decimal PopularRate = 0,
            [ParamDescription("基本色比例")] decimal BasicRate = 0,
            [ParamDescription("原始公司代码")] string originalCompanyID = "KM",
            [ParamDescription("原始年份")] int originalYearv = 0,
            [ParamDescription("原始季节")] string originalSeasonID = "",
            [ParamDescription("原始商品区域")] string originalParentAreaID = "",
            [ParamDescription("原始店铺名称")] string originalShopName = "",
            [ParamDescription("原始流行色比例")] decimal originalPopularRate = 0,
            [ParamDescription("原始基本色比例")] decimal originalBasicRate = 0)
        {

            #region 前端参数验证

            if (Yearv <= 0)
            {
                return Error($"更新失败！年份参数丢失，请刷新页面重试");
            }
            if (string.IsNullOrWhiteSpace(SeasonID))
            {
                return Error($"更新失败！季节参数丢失，请刷新页面重试");
            }

            if (string.IsNullOrWhiteSpace(ParentAreaID))
            {
                return Error($"更新失败！商品区域参数丢失，请刷新页面重试");
            }

            if (string.IsNullOrWhiteSpace(Shop))
            {
                return Error($"更新失败！店铺不能为空，请输入店铺的名称或者编码");
            }

            if (PopularRate < 0)
            {
                return Error($"插入失败！流行色占比不能小于0");
            }

            if (BasicRate < 0)
            {
                return Error("插入失败！基本色占比不能小于0");
            }

            #endregion

            #region 检测店铺是否存在并尝试获取shopID

            var sql = $@"select ShopID
--,ShopCode,ShopName,gv.dictValue as DistrictValue,gv.dictName as DistrictName
from ndw.ndw_Shop dw 
left join ndw.ndw_AreaProCity apc on dw.CompanyID = apc.CompanyID and dw.AreaID = apc.AreaID 
left join ndw.ndw_global_dictionary gv on apc.CompanyID = gv.CompanyID and apc.ParentAreaID = gv.dictValue 
where dw.CompanyID= '{UserInfo.UserCompanyId}' and gv.dictValue = '{ParentAreaID}' and (ShopCode = '{Shop}' or ShopName = '{Shop}')  limit 1 ";

            var o = Helper.ExecuteScalar(new VerticaCommand(sql));

            if (o == null)
            {
                return Error($"[{ParentAreaID}]不存在店铺[{Shop}],请检查店铺名称");
            }
            var shopId = string.Concat(o);

            #endregion


            #region 检测该配置是否已存在，避免插入重复数据
            //拼凑key，如果5个都一样证明是在改自身，不用判断重复，否则就要判断
            if (CompanyID != originalCompanyID || Yearv != originalYearv || SeasonID != originalSeasonID || ParentAreaID != originalParentAreaID || Shop != originalShopName)
            {
                sql = "select ShopID from ngoods.ngoods_PL_Bas_ShopColorRate " +
                    $"where CompanyID = '{CompanyID}' and " +
                    $"Yearv = {Yearv} and " +
                    $"SeasonID = '{SeasonID}' and " +
                    $"ParentAreaID = '{ParentAreaID}' and " +
                    $"ShopID = '{shopId}'  " +
                    "limit 1 ";
                o = Helper.ExecuteScalar(new VerticaCommand(sql));

                if (o != null)
                {
                    return Error($"修改失败！\r\n同年份、同季节、同区域、同店铺的配置已存在！");
                }
            }

            #endregion


            #region 更新数据

            sql = $@"UPDATE ngoods.ngoods_PL_Bas_ShopColorRate 
SET  Yearv = {Yearv},    SeasonID = '{SeasonID}',    ParentAreaID = '{ParentAreaID}',    ShopID = '{shopId}', PopularRate={PopularRate/100}, BasicRate={BasicRate/100}, CheckState=0, MofifyDTM='{DateTime.Now}', AllowUsed=1
WHERE CompanyID = '{originalCompanyID}' and Yearv = {originalYearv} and SeasonID = '{originalSeasonID}' and ParentAreaID = '{originalParentAreaID}' and ShopID = '{shopId}'
";
            int n = Helper.ExecuteNonQuery(new VerticaCommand(sql));

            #endregion

            if (n > 0)
            {
                return Success(0);
            }
            return Error("更新失败,请重试");
        }

        #endregion


        #region 获取店铺颜色占比(首页图表专用) GetPriceChart

        [MustLogin]
        [Description("获取店铺颜色占比(首页图表专用)")]
        [ReturnType(typeof(IList<PLBasShopColorRateModel.ChartModel>))]
        public ApiResult GetShopColorChart()
        {
            
            var list = new List<PLBasShopColorRateModel.ChartModel>();
            var plan = new PLPlanYMSetHandler().ReturnPLPlanYMSet();
            if (plan != null)
            {

                var sql = $@"select 
( select count(OnlyCode) from (select OnlyCode from ngoods.ngoods_PL_OnlyCode where CompanyID = '{UserInfo.UserCompanyId}' and yearv=2017 and Monthv=6 and ChooseFlag = 1           group by OnlyCode) a) LiuxingTotal,
( select count(OnlyCode) from (select OnlyCode from ngoods.ngoods_PL_OnlyCode where CompanyID = '{UserInfo.UserCompanyId}' and yearv=2017 and Monthv=6 and isnull(ChooseFlag,0)<>1 group by OnlyCode ) b) NotLiuxingTotal
";

                var dt = Helper.ExecuteDataTable(new VerticaCommand(sql, null));
                var tmpList = ModelConvertHelper<PLBasShopColorRateModel.TotalChartModel>.ConvertToModel(dt);
                if (tmpList != null && tmpList.Count > 0)
                {
                    //只会有一条数据
                    var model = tmpList[0];
                    list.Add(new PLBasShopColorRateModel.ChartModel()
                    {
                        Name = "流行色",
                        Total = model.LiuxingTotal
                    });

                    list.Add(new PLBasShopColorRateModel.ChartModel()
                    {
                        Name = "非流行色",
                        Total = model.NotLiuxingTotal
                    });
                }
            }

            return Success(list);
        }

        #endregion

    }

}
